package com.archetypus.projectschedule.common.data.people;


import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import com.archetypus.projectschedule.common.data.SQLServerDAOFactory;
import com.archetypus.projectschedule.common.dto.people.Person;
import com.archetypus.projectschedule.common.dto.people.PersonKey;
import com.archetypus.projectschedule.common.exceptions.ProjectSchedulerException;

public class SQLServerPersonDAO implements PersonDAO {

	@Override
	public void delete(Person person) throws ProjectSchedulerException {
		String sql = "DELETE FROM people WHERE id=?";
		Connection conn = null;
		PreparedStatement stmt = null;
		
		try{
			conn = SQLServerDAOFactory.createConnection();
			stmt = conn.prepareStatement(sql);
			stmt.setInt(1, person.getPersonKey().getId());
			stmt.executeUpdate();
		}catch(Exception ex){
			ex.printStackTrace();
			throw new ProjectSchedulerException("Could not delete register");
		}finally{
			try{
				stmt.close();
				conn.close();
			}catch(Exception e){}
		}
	}

	@Override
	public Person find(Person person) throws ProjectSchedulerException {
		String sql = "SELECT id, name, email FROM people WHERE id=?";
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		Person retrievedPerson = null;
		
		try{
			conn = SQLServerDAOFactory.createConnection();
			stmt = conn.prepareStatement(sql);
			stmt.setInt(1, person.getPersonKey().getId());
			rs = stmt.executeQuery();
			rs.next();
			retrievedPerson = new Person();
			retrievedPerson.setEmail(rs.getString("email"));
			retrievedPerson.setName(rs.getString("name"));
			retrievedPerson.setPersonKey(new PersonKey(rs.getInt("id")));
		}catch(Exception ex){
			ex.printStackTrace();
			throw new ProjectSchedulerException("Could not retrieve register");
		}finally{
			try{
				stmt.close();
				conn.close();
			}catch(Exception e){}
		}
		return retrievedPerson;
	}

	@Override
	public Person insert(Person person) throws ProjectSchedulerException {
		String sql = "INSERT INTO people(id,name,email) VALUES(?,?,?)";
		Connection conn = null;
		PreparedStatement stmt = null;
		
		try{
			conn = SQLServerDAOFactory.createConnection();
			stmt = conn.prepareStatement(sql);
			stmt.setInt(1, person.getPersonKey().getId());
			stmt.setString(2, person.getName());
			stmt.setString(3, person.getEmail());
			stmt.executeUpdate();
		}catch(Exception ex){
			ex.printStackTrace();
			throw new ProjectSchedulerException("Could not insert register");
		}finally{
			try{
				stmt.close();
				conn.close();
			}catch(Exception e){}
		}
		return person;
	}

	@Override
	public List<Person> list() throws ProjectSchedulerException {
		String sql = "SELECT id, name, email FROM people";
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		List<Person> peopleList = new ArrayList<Person>();
		
		try{
			conn = SQLServerDAOFactory.createConnection();
			stmt = conn.prepareStatement(sql);
			rs = stmt.executeQuery();
			while(rs.next()){
				Person p = new Person();
				p.setEmail(rs.getString("email"));
				p.setName(rs.getString("name"));
				p.setPersonKey(new PersonKey(rs.getInt("id")));
				peopleList.add(p);
			}
		}catch(Exception ex){
			ex.printStackTrace();
			throw new ProjectSchedulerException("Could not retrieve register");
		}finally{
			try{
				stmt.close();
				conn.close();
			}catch(Exception e){}
		}
		return peopleList;
	}

	@Override
	public void update(Person person) throws ProjectSchedulerException {
		String sql = "UPDATE people SET name=?, email=? WHERE id=?";
		Connection conn = null;
		PreparedStatement stmt = null;
		try{
			conn = SQLServerDAOFactory.createConnection();
			stmt = conn.prepareStatement(sql);
			stmt.setString(1, person.getName());
			stmt.setString(2, person.getEmail());
			stmt.setInt(3, person.getPersonKey().getId());
			stmt.executeUpdate();
		}catch(Exception ex){
			ex.printStackTrace();
			throw new ProjectSchedulerException("Could not update register");
		}finally{
			try{
				stmt.close();
				conn.close();
			}catch(Exception e){}
		}

	}

}
